// import mysql from "mysql" // 导入 mysql
const express = require("express") // 导入 body-parser中间件解析表单数据
const bodyParser = require("body-parser")
const mysql = require("mysql")
const connection = mysql.createConnection({
	host: "localhost", // 数据库主机地址，如果是本地数据库则使用localhost
	user: "root", // 数据库用户名
	password: "123456", // 数据库密码
	database: "sys", // 要连接的数据库名
})

// 连接数据库
connection.connect((err) => {
	if (err) {
		console.error("Error connecting to database:", err)
		return
	}
	console.log("Connected to database!")
})

const app = express()
// 解析 url-encoded格式的表单数据
app.use(express.urlencoded({ extended: true }))

// 解析json格式的表单数据
app.use(express.json())
//设置跨域访问
app.all("*", function (req, res, next) {
	res.header("Access-Control-Allow-Origin", "*")
	res.header("Access-Control-Allow-Headers", "X-Requested-With")
	res.header("Access-Control-Allow-Methods", "PUT,POST,GET,DELETE,OPTIONS")
	res.header("X-Powered-By", " 3.2.1")
	res.header("Content-Type", "application/json;charset=utf-8")
	next()
})

// 查询数据库
// 创建路由
app.get("/api/book/list", (req, res) => {
	// 查询数据库并返回数据
	connection.query("SELECT * FROM booklist", (err, results) => {
		console.log(err, "err")
		// console.log(results, "results")
		if (err) {
			console.error("Error querying database:", err)
			res.status(500).json({ error: "Internal server error" })
			return
		}
		res.json({
			code: "200",
			data: results,
		})
	})
})
// 书名搜索
app.get("/api/book/getName", (req, res) => {
	console.log(req.query, "req")
	const type = req.query.type
	const name = req.query.name
	let querySql = ``
	// let table=''
	// 出版社1 作者2 系列3 国家4 名称5
	switch (type) {
		case "1":
			querySql = `SELECT * FROM sys.booklist where publisher = '${name}';`
			break
		case "2":
			querySql = `SELECT * FROM sys.booklist where author like '%${name}%';`
			break
		case "3":
			querySql = `SELECT * FROM sys.booklist where series = '${name}';`
			break
		case "4":
			querySql = `SELECT * FROM sys.booklist where country like '${name}%';`
			break
		case "5":
			querySql = `SELECT * FROM sys.booklist where name like '%${name}%';`
		default:
			break
	}
	//   const querySql=`SELECT id, name, author, cover, isbn, binding, pages, price, publisher, translator, summary, subtitle, alt_title
	// FROM sys.booklist where name like '%${req.query.name}%';`

	connection.query(querySql, (err, results) => {
		console.log(results, "results")
		if (err) {
			console.error("Error querying database:", err)
			res.status(500).json({ error: "Internal server error" })
			return
		}
		res.json({
			code: "200",
			data: results,
		})
	})
})
// 查询单条数据
app.get("/api/book/getId", (req, res) => {
	// 判断库里是否已经存在isbn
	const querySql = `SELECT * FROM booklist WHERE isbn=${req.query.isbn};`
	connection.query(querySql, (err, results) => {
		console.log(results, "results")
		if (err) {
			console.error("Error querying database:", err)
			res.status(500).json({ error: "Internal server error" })
			return
		}
		res.json({
			code: "200",
			data: results,
		})
	})
})

// 插入
app.post("/api/book/add", (req, res) => {
	const insertSql = `INSERT INTO booklist (name,author,cover,isbn,series,publisher,country,binding,tags,author_intro,translator,subtitle,price,pages,pubdate) VALUES ('${req.body.title}','${req.body.author}','${req.body.cover}','${req.body.isbn}','${req.body.series}','${req.body.publisher}','${req.body.country}','${req.body.binding}','${req.body.tags}','${req.body.author_intro}','${req.body.translator}','${req.body.subtitle}','${req.body.price}','${req.body.pages}','${req.body.pubdate}')`
	connection.query(insertSql, (err, results) => {
		// console.log(err, "err")
		console.log(results, "results")
		if (err) {
			console.error("Error querying database:", err)
			res.status(500).json({ error: "Internal server error" })
			return
		}
		res.json({
			code: "200",
			data: results,
		})
	})
})
// 查询分类---- 失效
// app.get("/api/classify/list", (req, res) => {
//   // 查询所有分类
//   const querySql = "SELECT * FROM category";
//   connection.query(querySql, (err, results) => {
//     if (err) {
//       console.error("Error querying database:", err);
//       res.status(500).json({ error: "Internal server error" });
//       return;
//     }
//     res.json({
//       code: "200",
//       data: results,
//     });
//   });
// });

// 插入分类
app.post("/api/classify/add", (req, res) => {
	// 查询表里的分类
	// 出版社1 作者2 系列3 国家4
	// 使用Promise处理异步查询
	const body = req.body
	const type = body.type
	const name = body.name.split(",")
	let table = ""
	switch (type) {
		case "1":
			table = "publisher"
			break
		case "2":
			table = "author"
			break
		case "3":
			table = "series"
			break
		case "4":
			table = "country"
			break
	}
	const processCategories = async () => {
		let returnRes = []

		for (let i in name) {
			// 添加引号避免SQL注入
			const querySql = `SELECT * FROM ${table} WHERE name="${name[i]}"`

			try {
				// 使用Promise包装查询
				const results = await new Promise((resolve, reject) => {
					connection.query(querySql, (err, results) => {
						if (err) reject(err)
						resolve(results)
					})
				})
				// 如果分类不存在则插入
				if (!results || !results.length) {
					const insertSql = `INSERT INTO ${table} (name,cover) VALUES ("${name[i]}","${body.cover}")`
					const insertResult = await new Promise((resolve, reject) => {
						connection.query(insertSql, (error, rst) => {
							if (error) reject(error)
							resolve(rst)
						})
					})
					returnRes.push(insertResult)
				}
			} catch (error) {
				console.error("数据库查询错误:", error)
				return res.status(500).json({ error: "Internal server error" })
			}
		}

		// 所有操作完成后返回结果
		return res.json({
			code: "200",
			data: returnRes,
		})
	}

	// 执行处理函数
	processCategories()
})
// 获得分类下的数据
app.get("/api/classify/list", (req, res) => {
	// 查询所有分类
	const type = req.query.type
	let table = ""
	switch (type) {
		case "1":
			table = "publisher"
			break
		case "2":
			table = "author"
			break
		case "3":
			table = "series"
			break
		case "4":
			table = "country"
			break
		default:
			break
	}
	const querySql = `SELECT * FROM ${table}`
	connection.query(querySql, (err, results) => {
		if (err) {
			console.error("Error querying database:", err)
			res.status(500).json({ error: "Internal server error" })
			return
		}
		res.json({
			code: "200",
			data: results,
		})
	})
})

// 启动服务器
const PORT = 8888
app.listen(PORT, () => {
	console.log(`Server is running on http://localhost:${PORT}`)
})
